DELETE FROM [dbo].[role];
GO
SET IDENTITY_INSERT[dbo].[role]  ON
INSERT INTO role (id, rolename) values (1, 'ROLE_ADMIN');
INSERT INTO role (id, rolename) values (2, 'ROLE_GRANTS');
INSERT INTO role (id, rolename) values (3, 'ROLE_FINANCE');
INSERT INTO role (id, rolename) values (4, 'ROLE_STAFF');
INSERT INTO role (id, rolename) values (5, 'ROLE_KM');
INSERT INTO role (id, rolename) values (6, 'ROLE_SENIOR_MANAGEMENT');
GO
SET IDENTITY_INSERT[dbo].[role]  OFF
GO

DELETE FROM [dbo].[userrole];
GO
/* ALTER userrole TABLE */
ALTER TABLE [dbo].[userrole] DROP CONSTRAINT [PK__userrole__3213E83E16CE6296]
ALTER TABLE [dbo].[userrole] DROP COLUMN [id]
GO
ALTER TABLE [dbo].[userrole] ALTER COLUMN [roleid] int NOT NULL 
ALTER TABLE [dbo].[userrole] ALTER COLUMN [userinfoid] int NOT NULL 
GO
ALTER TABLE [dbo].[userrole] ADD PRIMARY KEY ([userinfoid],[roleid])
GO

/* INSERT userrole DATA */
INSERT INTO userrole (userinfoid, roleid) values (211, 1);
INSERT INTO userrole (userinfoid, roleid) values (12, 1);
GO

/* ALTER roleprivileges TABLE */
ALTER TABLE [dbo].[roleprivileges] DROP CONSTRAINT [PK__rolepriv__3213E83E0B5CAFEA] 
ALTER TABLE [dbo].[roleprivileges] DROP COLUMN [id]
GO
ALTER TABLE [dbo].[roleprivileges] ALTER COLUMN [roleid] int NOT NULL 
ALTER TABLE [dbo].[roleprivileges] ALTER COLUMN [link] int NOT NULL 
GO
ALTER TABLE [dbo].[roleprivileges] ADD PRIMARY KEY ([roleid], [link])
GO

DELETE FROM [dbo].[roleprivileges];
GO
/* INSERT DATA */
/* ROME_ADMIN */
INSERT INTO roleprivileges (roleid, link) values (1, 1); /* Add Donor */
INSERT INTO roleprivileges (roleid, link) values (1, 3); /* Donor List */
INSERT INTO roleprivileges (roleid, link) values (1, 27); /* Add Donor Award */
INSERT INTO roleprivileges (roleid, link) values (1, 28); /* View - Donor Award */
INSERT INTO roleprivileges (roleid, link) values (1, 30); /* Add Project */
INSERT INTO roleprivileges (roleid, link) values (1, 92); /* Add Partner Grant */
INSERT INTO roleprivileges (roleid, link) values (1, 31); /* View - Projects and Grants */
INSERT INTO roleprivileges (roleid, link) values (1, 41); /* Add Payment Request */
INSERT INTO roleprivileges (roleid, link) values (1, 42); /* View - Payment Request */
INSERT INTO roleprivileges (roleid, link) values (1, 45); /* Instructions */
INSERT INTO roleprivileges (roleid, link) values (1, 94); /* Project Catalogue */
INSERT INTO roleprivileges (roleid, link) values (1, 49); /* Reports List */
INSERT INTO roleprivileges (roleid, link) values (1, 50); /* Email Grants Management */
INSERT INTO roleprivileges (roleid, link) values (1, 51); /* Reports Computer Problems to ... */
INSERT INTO roleprivileges (roleid, link) values (1, 52); /* CMRS System REquests - Add User, etc. */
INSERT INTO roleprivileges (roleid, link) values (1, 57); /* Manage Users */
INSERT INTO roleprivileges (roleid, link) values (1, 58); /* View Users */
INSERT INTO roleprivileges (roleid, link) values (1, 53); /* Manage Roles */
INSERT INTO roleprivileges (roleid, link) values (1, 54); /* View Roles */
INSERT INTO roleprivileges (roleid, link) values (1, 63); /* Manage Regions */
INSERT INTO roleprivileges (roleid, link) values (1, 73); /* View Regions */
INSERT INTO roleprivileges (roleid, link) values (1, 74); /* Manage Countries */
INSERT INTO roleprivileges (roleid, link) values (1, 75); /* View Countries */
INSERT INTO roleprivileges (roleid, link) values (1, 76); /* Manage States */
INSERT INTO roleprivileges (roleid, link) values (1, 77); /* View States */
INSERT INTO roleprivileges (roleid, link) values (1, 78); /* Manage Field Offices */
INSERT INTO roleprivileges (roleid, link) values (1, 79); /* View Field Offices */
INSERT INTO roleprivileges (roleid, link) values (1, 65); /* Manage Donor Award Types */
INSERT INTO roleprivileges (roleid, link) values (1, 80); /* View Donor Award Types */
INSERT INTO roleprivileges (roleid, link) values (1, 64); /* Manage Donor Award Statuses */
INSERT INTO roleprivileges (roleid, link) values (1, 81); /* View Donor Award Statuses */
INSERT INTO roleprivileges (roleid, link) values (1, 67); /* Manage Project Types */
INSERT INTO roleprivileges (roleid, link) values (1, 82); /* View Project Types */
/*INSERT INTO roleprivileges (roleid, link) values (1, 67);  Manage Project Types */
INSERT INTO roleprivileges (roleid, link) values (1, 66); /* Manage Project Statuses */
INSERT INTO roleprivileges (roleid, link) values (1, 83); /* View Project Statuses */
INSERT INTO roleprivileges (roleid, link) values (1, 71); /* Manage Payment Request Types */
INSERT INTO roleprivileges (roleid, link) values (1, 84); /* View Payment Request Types */
INSERT INTO roleprivileges (roleid, link) values (1, 70); /* Manage Payment Request Statuses */
INSERT INTO roleprivileges (roleid, link) values (1, 85); /* View Payment Request Statuses */
INSERT INTO roleprivileges (roleid, link) values (1, 62); /* Manage Account Codes */
INSERT INTO roleprivileges (roleid, link) values (1, 88); /* View Account Codes */
INSERT INTO roleprivileges (roleid, link) values (1, 55); /* Manage KM Organization Types */
INSERT INTO roleprivileges (roleid, link) values (1, 89); /* View KM Organization Types */
INSERT INTO roleprivileges (roleid, link) values (1, 90); /* Manage KM Organization Keywords */
INSERT INTO roleprivileges (roleid, link) values (1, 91); /* View KM Organization Keywords */

/* ROLE_GRANTS */
INSERT INTO roleprivileges (roleid, link) values (2, 50); /* Email Grants Management */
INSERT INTO roleprivileges (roleid, link) values (2, 51); /* Reports Computer Problems to ... */
INSERT INTO roleprivileges (roleid, link) values (2, 52); /* CMRS System REquests - Add User, etc. */
INSERT INTO roleprivileges (roleid, link) values (2, 1); /* Add Donor */
INSERT INTO roleprivileges (roleid, link) values (2, 3); /* Donor List */
INSERT INTO roleprivileges (roleid, link) values (2, 27); /* Add Donor Award */
INSERT INTO roleprivileges (roleid, link) values (2, 28); /* View - Donor Award */
INSERT INTO roleprivileges (roleid, link) values (2, 30); /* Add Project */
INSERT INTO roleprivileges (roleid, link) values (2, 92); /* Add Partner Grant */
INSERT INTO roleprivileges (roleid, link) values (2, 31); /* View - Projects and Grants */
INSERT INTO roleprivileges (roleid, link) values (2, 41); /* Add Payment Request */
INSERT INTO roleprivileges (roleid, link) values (2, 42); /* View - Payment Request */
INSERT INTO roleprivileges (roleid, link) values (2, 45); /* Instructions */
INSERT INTO roleprivileges (roleid, link) values (2, 94); /* Project Catalogue */
INSERT INTO roleprivileges (roleid, link) values (2, 49); /* Reports List */

/* ROLE_FINANCE */
INSERT INTO roleprivileges (roleid, link) values (3, 41); /* Add Payment Request */
INSERT INTO roleprivileges (roleid, link) values (3, 42); /* View - Payment Request */
INSERT INTO roleprivileges (roleid, link) values (3, 45); /* Instructions */
INSERT INTO roleprivileges (roleid, link) values (3, 49); /* Reports List */

/* ROLE_STAFF */
INSERT INTO roleprivileges (roleid, link) values (4, 3); /* Donor List */
INSERT INTO roleprivileges (roleid, link) values (4, 28); /* View - Donor Award */
INSERT INTO roleprivileges (roleid, link) values (4, 31); /* View - Projects and Grants */
INSERT INTO roleprivileges (roleid, link) values (4, 41); /* Add Payment Request */
INSERT INTO roleprivileges (roleid, link) values (4, 42); /* View - Payment Request */
INSERT INTO roleprivileges (roleid, link) values (4, 45); /* Instructions */
INSERT INTO roleprivileges (roleid, link) values (4, 94); /* Project Catalogue */
INSERT INTO roleprivileges (roleid, link) values (4, 49); /* Reports List */

/* ROLE_KM */
INSERT INTO roleprivileges (roleid, link) values (5, 3); /* Donor List */
INSERT INTO roleprivileges (roleid, link) values (5, 28); /* View - Donor Award */
INSERT INTO roleprivileges (roleid, link) values (5, 31); /* View - Projects and Grants */
INSERT INTO roleprivileges (roleid, link) values (5, 41); /* Add Payment Request */
INSERT INTO roleprivileges (roleid, link) values (5, 42); /* View - Payment Request */
INSERT INTO roleprivileges (roleid, link) values (5, 45); /* Instructions */
INSERT INTO roleprivileges (roleid, link) values (5, 94); /* Project Catalogue */
INSERT INTO roleprivileges (roleid, link) values (5, 49); /* Reports List */

/* ROLE_SENIOR_MANAGEMENT */
INSERT INTO roleprivileges (roleid, link) values (6, 3); /* Donor List */
INSERT INTO roleprivileges (roleid, link) values (6, 28); /* View - Donor Award */
INSERT INTO roleprivileges (roleid, link) values (6, 31); /* View - Projects and Grants */
INSERT INTO roleprivileges (roleid, link) values (6, 41); /* Add Payment Request */
INSERT INTO roleprivileges (roleid, link) values (6, 42); /* View - Payment Request */
INSERT INTO roleprivileges (roleid, link) values (6, 45); /* Instructions */
INSERT INTO roleprivileges (roleid, link) values (6, 94); /* Project Catalogue */
INSERT INTO roleprivileges (roleid, link) values (6, 49); /* Reports List */

GO
